package com.molichuxing.gateway.utils;


import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.time.LocalDateTime;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * poi生成excel util
 *
 * @author lws
 * @date 2019年12月13日
 */
public class ExcelUtil {

    private final static String FONT_NAME_DENG_XIAN = "等线";



    /**
     * 定制单元格边框
     */
    public static void getNewCenterStyle(CellStyle style) {
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        /* return style; */
    }

    public static void setReginBolder(CellRangeAddress region, Sheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
    }




    public static void setCell(XSSFWorkbook workbook, XSSFSheet sheet, int rowIndex, int cellIndex, String cellValue, Boolean bold, int fontSize, String fontName) {
        setCell(workbook,  sheet,  rowIndex,  cellIndex,  cellValue,  bold,  fontSize,  fontName,false,0,0,0,0);
    }

    public static void setCell(XSSFWorkbook workbook, XSSFSheet sheet, int rowIndex, int cellIndex, String cellValue, Boolean bold, int fontSize, String fontName, Boolean isRegion, int firstRow, int lastRow, int firstCol, int lastCol) {
        XSSFRow titleRow = sheet.getRow((short) rowIndex);
        if(titleRow==null){
            titleRow = sheet.createRow((short) rowIndex);
        }
        XSSFCell cell = titleRow.createCell(cellIndex);
        cell.setCellValue(cellValue);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);  // 设置水平方向字体居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直方向字体居中

        //设置字体
        Font font = workbook.createFont();
        font.setBold(bold); //加粗
        font.setFontName(fontName); //字体
        font.setFontHeightInPoints((short) fontSize);
        cellStyle.setFont(font);
        //设置字体边框
        ExcelUtil.getNewCenterStyle(cellStyle);
        cell.setCellStyle(cellStyle);

        if (isRegion) {
            CellRangeAddress region = new CellRangeAddress(firstRow,  lastRow,  firstCol,  lastCol);
            ExcelUtil.setReginBolder(region, sheet);
            try {
                sheet.addMergedRegion(region);
            }catch (Exception e){
                System.out.println(firstRow+" "+lastRow+" "+firstCol+" "+lastCol);
                e.printStackTrace();
            }
        }
    }

    static CellStyle setTableCellStyle(XSSFWorkbook workbook, HorizontalAlignment hAlign, VerticalAlignment vAlign, Boolean bold, String fontName, int fontSize){
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(hAlign);  // 设置水平方向字体居中
        cellStyle.setVerticalAlignment(vAlign); //设置垂直方向字体居中

        //设置字体
        Font font = workbook.createFont();
        font.setBold(bold); //加粗
        font.setFontName(fontName); //字体
        font.setFontHeightInPoints((short) fontSize);
        cellStyle.setFont(font);
        //设置字体边框
        ExcelUtil.getNewCenterStyle(cellStyle);
        return cellStyle;

    }



    public static void buildNomalSheet(XSSFWorkbook book, String title, final List data, final String[] propNames, final String[] headStrs){
        XSSFSheet sheet = book.createSheet(title);
        sheet.setDefaultColumnWidth(20);
        sheet.autoSizeColumn(1);
        sheet.setDefaultRowHeight((short) 400);

        CellStyle titleStyle = setTableCellStyle(book, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, true, FONT_NAME_DENG_XIAN, 14);

        XSSFRow row;
        XSSFCell cell;
        row = sheet.createRow(0);
        for (int i = 0; i < propNames.length; i++) {
            if (null == propNames[i] || "".equals(propNames[i])) {
                continue;
            }
            cell = row.createCell(i);
            cell.setCellValue(headStrs[i]);
            cell.setCellStyle(titleStyle);
        }
        final int dataEnd = data.size(), dataStart = 0;
        Field[] filed = new Field[0];
        //无数据的情况处理
        if (data != null && data.size() > 0) {
            filed = data.get(0).getClass().getDeclaredFields();
        }
        Map hm = new HashMap();

        for (Field f : filed) {
            hm.put(f.getName(), f.getName());
        }

        Object value = null;

        CellStyle valueStyle = setTableCellStyle(book, HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, false, FONT_NAME_DENG_XIAN, 14);

        int excelStartRow = 1;
        for (int datarow = dataStart; datarow < dataEnd; excelStartRow++, datarow++) {
            final Object bean = data.get(datarow);
            row = sheet.createRow(excelStartRow);
            for (int col = 0; col < propNames.length; col++) {
                cell = row.createCell(col);
                final String field = propNames[col];
                if (null == field || "".equals(field)) {
                    continue;
                }

                if (hm.containsKey(field)) {
                    try {
                        value = PropertyUtils.getSimpleProperty(bean, field);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    }
                    if (value == null) {
                        value = " ";
                    }
                } else value = "";

                if (value instanceof Date) {
                    //xc.setCellValue(DateUtils.formatTime((Date)value));
                    String dateValue = DateUtil.format("yyyy-MM-dd HH:mm:ss", (Date) value);
                    cell.setCellValue(dateValue);
                }else if(value instanceof LocalDateTime){
                    String dateValue = DateUtil.formatDate((LocalDateTime) value,DateUtil.YEAR_SECOND);
                    cell.setCellValue(dateValue);
                } else {
                    cell.setCellValue(value.toString());
                }
                cell.setCellStyle(valueStyle);
            }
        }
    }

}


